﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient


Public Class VacanteDB

    Public Shared Sub InsertarVacante(ByVal id_cargo_vacante As Integer, ByVal expira As Date)
        Dim cmd As New SqlCommand("spInsertarVacante", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_cargo_vacante", id_cargo_vacante)
        cmd.Parameters.AddWithValue("@expira", expira)

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Function InsertarRequerimientoVacanteExperiencia(ByVal id_cargo_vacante As Integer, ByVal expira As Date, ByVal edad_desde As Integer, ByVal edad_hasta As Integer, ByVal id_vacante As Integer, ByVal id_experiencia As Integer, ByVal antiguedad As Integer) As Integer
        Dim cmd As New SqlCommand("spInsertarRequerimientosVacanteExperiencia", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_cargo_vacante", id_cargo_vacante)
        cmd.Parameters.AddWithValue("@expira", expira)
        cmd.Parameters.AddWithValue("@edad_desde", edad_desde)
        cmd.Parameters.AddWithValue("@edad_hasta", edad_hasta)
        cmd.Parameters.AddWithValue("@id_experiencia", id_experiencia)
        cmd.Parameters.AddWithValue("@antiguedad", antiguedad)
        cmd.Parameters.AddWithValue("@id_vacante", id_vacante).Direction = ParameterDirection.InputOutput
        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

        Return cmd.Parameters("@id_vacante").Value

    End Function

    Public Shared Function InsertarRequerimientoVacanteEstudios(ByVal id_cargo_vacante As Integer, ByVal expira As Date, ByVal edad_desde As Integer, ByVal edad_hasta As Integer, ByVal id_vacante As Integer, ByVal id_estudio As Integer, ByVal descripcion_estudio As String) As Integer
        Dim cmd As New SqlCommand("spInsertarRequerimientosVacanteEstudios", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_cargo_vacante", id_cargo_vacante)
        cmd.Parameters.AddWithValue("@expira", expira)
        cmd.Parameters.AddWithValue("@edad_desde", edad_desde)
        cmd.Parameters.AddWithValue("@edad_hasta", edad_hasta)
        cmd.Parameters.AddWithValue("@id_estudio", id_estudio)
        cmd.Parameters.AddWithValue("@descripcion_estudio", descripcion_estudio)
        cmd.Parameters.AddWithValue("@id_vacante", id_vacante).Direction = ParameterDirection.InputOutput
        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output

        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

        Return cmd.Parameters("@id_vacante").Value

    End Function

    Public Shared Function ObtenerUltimaVacante() As DataSet
        Dim cmd As New SqlCommand("spObtenerUltimaVacante", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        da.SelectCommand = cmd
        da.Fill(ds, "UltimaVacante")
        Return ds

    End Function

    Public Shared Sub EliminarVacante(ByVal id_vacante As Integer)
        Dim cmd As New SqlCommand("spDeleteVacante", DB.Conexion)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_vacante", id_vacante)
        cmd.Parameters.Add("@error", SqlDbType.Int)
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If
    End Sub

    Public Shared Sub EliminarExperienciaRequerimiento(ByVal id_requerimiento As Integer, ByVal id_detalle As Integer)
        Dim cmd As New SqlCommand("spDeleteExperienciaRequerimiento", DB.Conexion)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_requerimiento", id_requerimiento)
        cmd.Parameters.AddWithValue("@id_detalle", id_detalle)
        cmd.Parameters.Add("@error", SqlDbType.Int)
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If
    End Sub

    Public Shared Sub EliminarEstudioRequerimiento(ByVal id_requerimiento As Integer, ByVal id_detalle As Integer)
        Dim cmd As New SqlCommand("spDeleteEstudioRequerimiento", DB.Conexion)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_requerimiento", id_requerimiento)
        cmd.Parameters.AddWithValue("@id_detalle", id_detalle)
        cmd.Parameters.Add("@error", SqlDbType.Int)
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If
    End Sub
End Class
